﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Data;

namespace Registro
{
    public class ControladorExcel
    {
        Excel.Application oXL;
        Excel._Workbook oWB;
        Excel._Worksheet oSheet;
        Excel.Range oRng;
        BaseDatos bd = new BaseDatos();

        public Boolean PlantillaExcel()
        {

            try
            {
                //Start Excel and get Application object.
                oXL = new Excel.Application();
                oXL.Visible = true;
                //Get a new workbook.
                oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
                oSheet = (Excel._Worksheet)oWB.ActiveSheet;
                //Add table headers going cell by cell.
                oSheet.Cells[1, 1] = "UNIVERSIDAD TECNOLÓGICA DE HERMOSILLO, SONORA";
                oSheet.Cells[3, 1] = "FORMATO DE ASISTENCIA DIARIA";
                oSheet.Cells[5, 1] = "CARRERA:TECNOLOGÍAS DE LA INFORMACIÓN Y LA COMUNICACIÓN";
                oSheet.Cells[8, 1] = "DIAS";
                oSheet.Cells[10, 1] = "LUNES";
                oSheet.Cells[11, 1] = "MARTES";
                oSheet.Cells[12, 1] = "MIERCOLES";
                oSheet.Cells[13, 1] = "JUEVES";
                oSheet.Cells[14, 1] = "VIERNES";
                oSheet.Cells[15, 1] = "PROM. ASISTENCIA SEMANAL";
                oSheet.Cells[16, 1] = "PORC. ASISTENCIA SEMANAL";
                //oSheet.Cells[15, 2] = "=SUMA(B10:B14)/5";
                llenarPromedio();
                llenarPorcentaje();
                oSheet.get_Range("A15").WrapText = true;
                //Format A1:D1 as bold, vertical alignment = center.
                oSheet.get_Range("A1", "A5").Font.Bold = true;
                oSheet.get_Range("A1", "D1").Font.Size = 16;

                DataSet ds = bd.LeerRegistros("SELECT nombre FROM grupos;");
                int numero = ds.Tables[0].Rows.Count;
                ponerFondo(numero);

                ////oSheet.get_Range("A8", "J").Interior.ColorIndex = 15;

                oSheet.get_Range("A1", "C1").MergeCells(Missing.Value);
                oSheet.get_Range("A3", "C3").MergeCells(Missing.Value);
                oSheet.get_Range("A5", "C5").MergeCells(Missing.Value);


                //AutoFit columns A:D.
                oRng = oSheet.get_Range("A1", "D1");
                oRng.EntireColumn.AutoFit();
                //Make sure Excel is visible and give the user control
                //of Microsoft Excel's lifetime.
                oXL.Visible = true;
                oXL.UserControl = true;
                return true;
            }
            catch (Exception theException)
            {
                return false;
            }
        }

        public Boolean llenarGruposExcel()
        {
            DataSet ds = bd.LeerRegistros("SELECT nombre FROM grupos;");
            int m = 2;
            for (int c = 0; c < ds.Tables[0].Rows.Count; c++)
            {
                oSheet.Cells[8, m + c] = ds.Tables[0].Rows[c][0].ToString();
            }
            return true;
        }

        public Boolean llenarTotalAlumnosExcel()
        {
            DataSet ds = bd.LeerRegistros("SELECT cantidadAlumnos FROM GruposAlumnos order by idGrupo;");
            int m = 2;
            for (int c = 0; c < ds.Tables[0].Rows.Count; c++)
            {
                oSheet.Cells[9, m + c] = ds.Tables[0].Rows[c][0].ToString();
            }
            return true;
        }

        public Boolean llenarAsistenciaExcel(String fecha)
        {
            int m = 10;
            string fecha2 = fecha;
            int cont = 1;
            DataSet ds = bd.LeerRegistros("SELECT nombre FROM grupos;");
            for (int c = 1; c <= ds.Tables[0].Rows.Count; c++)
            {
                if (c == 2)
                {
                    fecha = fecha2;
                }
                if (c == 3)
                {
                    fecha = fecha2;
                }
                cont = cont + 1;
                DataSet ds2 = bd.LeerRegistros("SELECT cantidadAlumnos,diaSemana FROM registros,Horarios,Grupos WHERE Horarios.idHorario=Registros.idHorario AND Grupos.idGrupo=Horarios.idGrupo AND Grupos.idGrupo=" + c + " Group by diaSemana;");

                for (int c2 = 0; c2 < ds2.Tables[0].Rows.Count; c2++)
                {
                    try
                    {
                        if (c2 < 6)
                        {
                            DataSet ds3 = bd.LeerRegistros("SELECT cantidadAlumnos,diaSemana FROM registros,Horarios,Grupos WHERE Horarios.idHorario=Registros.idHorario AND Grupos.idGrupo=Horarios.idGrupo AND Grupos.idGrupo=" + c + " AND fechaRegistro='" + fecha + "' Group by diaSemana;");
                            oSheet.Cells[c2 + m, cont] = ds3.Tables[0].Rows[0][0].ToString();
                            DataSet ds4 = bd.LeerRegistros("CALL incrementarFecha('" + fecha + "')");
                            fecha = ds4.Tables[0].Rows[0][0].ToString();
                        }
                    }
                    catch
                    {

                    }
                }
            }
            return true;
        }

        public void ponerFondo(int num)
        {
            switch (num)
            {
                case 1: oSheet.get_Range("A8", "B9").Interior.ColorIndex = 15; break;
                case 2: oSheet.get_Range("A8", "C9").Interior.ColorIndex = 15; break;
                case 3: oSheet.get_Range("A8", "D9").Interior.ColorIndex = 15; break;
                case 4: oSheet.get_Range("A8", "E9").Interior.ColorIndex = 15; break;
                case 5: oSheet.get_Range("A8", "F9").Interior.ColorIndex = 15; break;
                case 6: oSheet.get_Range("A8", "G9").Interior.ColorIndex = 15; break;
                case 7: oSheet.get_Range("A8", "H9").Interior.ColorIndex = 15; break;
                case 8: oSheet.get_Range("A8", "I9").Interior.ColorIndex = 15; break;
                case 9: oSheet.get_Range("A8", "J9").Interior.ColorIndex = 15; break;
                case 10: oSheet.get_Range("A8", "K9").Interior.ColorIndex = 15; break;
                case 11: oSheet.get_Range("A8", "L9").Interior.ColorIndex = 15; break;
                case 12: oSheet.get_Range("A8", "M9").Interior.ColorIndex = 15; break;
                case 13: oSheet.get_Range("A8", "N9").Interior.ColorIndex = 15; break;
                case 14: oSheet.get_Range("A8", "O9").Interior.ColorIndex = 15; break;
                case 15: oSheet.get_Range("A8", "P9").Interior.ColorIndex = 15; break;
            }
        }

        public void llenarPromedio()
        {
            int c2 = 2;
            DataSet ds = bd.LeerRegistros("SELECT nombre FROM grupos;");
            for (int c = 0; c < ds.Tables[0].Rows.Count; c++)
            {
                if (c == 0)
                {
                    oSheet.Cells[15, c2 + c] = "=SUM(B10:B14)/5";
                }
                if (c == 1)
                {
                    oSheet.Cells[15, c2 + c] = "=SUM(C10:C14)/5";
                }
                if (c == 2)
                {
                    oSheet.Cells[15, c2 + c] = "=SUM(D10:D14)/5";
                }
                if (c == 3)
                {
                    oSheet.Cells[15, c2 + c] = "=SUM(E10:E14)/5";
                }
                if (c == 4)
                {
                    oSheet.Cells[15, c2 + c] = "=SUM(F10:F14)/5";
                }
                if (c == 5)
                {
                    oSheet.Cells[15, c2 + c] = "=SUM(G10:G14)/5";
                }
                if (c == 6)
                {
                    oSheet.Cells[15, c2 + c] = "=SUM(H10:H14)/5";
                }
                if (c == 7)
                {
                    oSheet.Cells[15, c2 + c] = "=SUM(I10:I14)/5";
                }
                if (c == 8)
                {
                    oSheet.Cells[15, c2 + c] = "=SUM(J10:J14)/5";
                }
                if (c == 19)
                {
                    oSheet.Cells[15, c2 + c] = "=SUM(K10:K14)/5";
                }
                if (c == 10)
                {
                    oSheet.Cells[15, c2 + c] = "=SUM(L10:L14)/5";
                }
                if (c == 11)
                {
                    oSheet.Cells[15, c2 + c] = "=SUM(M10:M14)/5";
                }
                if (c == 12)
                {
                    oSheet.Cells[15, c2 + c] = "=SUM(N10:N14)/5";
                }
                if (c == 13)
                {
                    oSheet.Cells[15, c2 + c] = "=SUM(O10:O14)/5";
                }
            }
        }

        public void llenarPorcentaje()
        {
            int c2 = 2;
            DataSet ds = bd.LeerRegistros("SELECT nombre FROM grupos;");
            for (int c = 0; c < ds.Tables[0].Rows.Count; c++)
            {
                if (c == 0)
                {
                    oSheet.Cells[16, c2 + c] = "=(B15/B9)*100";
                }
                if (c == 1)
                {
                    oSheet.Cells[16, c2 + c] = "=(C15/C9)*100";
                }
                if (c == 2)
                {
                    oSheet.Cells[16, c2 + c] = "=(D15/D9)*100";
                }
                if (c == 3)
                {
                    oSheet.Cells[16, c2 + c] = "=(E15/E9)*100";
                }
                if (c == 4)
                {
                    oSheet.Cells[16, c2 + c] = "=(F15/F9)*100";
                }
                if (c == 5)
                {
                    oSheet.Cells[16, c2 + c] = "=(G15/G9)*100";
                }
                if (c == 6)
                {
                    oSheet.Cells[16, c2 + c] = "=(H15/H9)*100";
                }
                if (c == 7)
                {
                    oSheet.Cells[16, c2 + c] = "=(I15/I9)*100";
                }
                if (c == 8)
                {
                    oSheet.Cells[16, c2 + c] = "=(J15/J9)*100";
                }
                if (c == 19)
                {
                    oSheet.Cells[16, c2 + c] = "=(K15/K9)*100";
                }
                if (c == 10)
                {
                    oSheet.Cells[16, c2 + c] = "=(L15/L9)*100";
                }
                if (c == 11)
                {
                    oSheet.Cells[16, c2 + c] = "=(M15/M9)*100";
                }
                if (c == 12)
                {
                    oSheet.Cells[16, c2 + c] = "=(N15/N9)*100";
                }
                if (c == 13)
                {
                    oSheet.Cells[16, c2 + c] = "=(O15/O9)*100";
                }
            }
        }
    }
}